<?php
/**
 * @name DashboardController.php
 * @copyright (c) 2011
 * @author Arsene Tochemey GANDOTE<arsene.gandote@yahoo.com>
 */
class ReportController extends AppController
{
    # Controller name
    var $name = 'Report';
    # set the model to use
    var $uses = array(
        'Sale','SaleItem','SaleCreditPayment'
    );
    # Set the layout to use
    var $layout = 'report_layout';

    # Controllers Actions
    /**
     * This function checks the authenticity of the user.
     * @name beforeFilter
     * @param void
     * @return void
     * @access public
     * */
    function beforeFilter()
    {
        parent::beforeFilter();
        parent::setUpGlobalConfig();
    }

    /**
     * This is a kind of home page of the controller Dashboard.
     * @name index
     * @param void
     * @var mixed $loggedUser array containing the logged user session data.
     * @return void
     * @access public
     * */
    function index()
    {
        $currency_types = $this->currency_types;

        $currency_opts = array();
        foreach ($currency_types as $key => $value) {
            $currency_opts[] = array('name' => $value, 'value' => $key);
        }

        $page_title = 'Sales Report Menu';
        //debug($this->get_customers_report('$'));

        $this->set(compact('page_title','currency_opts'));
    }

    function customer_sales()
    {
        $currency_types = $this->currency_types;

        $currency_opts = array();
        foreach ($currency_types as $key => $value) {
            $currency_opts[] = array('name' => $value, 'value' => $key);
        }

        $page_title = 'Sales Report';
        //debug($this->get_customers_report('$'));

        $this->set(compact('page_title','currency_opts'));
    }

    /**
     * @name get
     * @param void
     * @return Json Data
     */
    function get($currency = null, $pdata_type = null)
    {
        # disable the rendering of the layout
        $data_type = isset($pdata_type) ? $pdata_type : 'get';

        if ($this->request->is('ajax')) {
            /**  Get posted data */
            $page = isset($_POST['page']) ? $_POST['page'] : 1;
            /** The current page */
            $sortname = isset($_POST['sortname']) ? $_POST['sortname'] : 'id';
            /** Sort column */
            $sortorder = isset($_POST['sortorder']) ? $_POST['sortorder'] : 'desc';
            /** Sort order */
            $qtype = isset($_POST['qtype']) ? $_POST['qtype'] : '';
            /** Search column */
            $search_query = isset($_POST['query']) ? $_POST['query'] : '';
            /** Search string */
            $rp = isset($_POST['rp']) ? $_POST['rp'] : 10;
            $limit = $rp;
            $start = ($page - 1) * $rp;
            $tran_currency = isset($_POST['currency']) ? $_POST['currency'] : 'GHc';

            $params = array(
                'tran_currency' =>$tran_currency,
                //'sortname' =>$sortname,
                //'sortorder' =>$sortorder,
                //'start' =>$start,
                //'limit' =>$limit,
            );
        }
        else {
            if($this->request->is('post')){
                $ids = explode(',', $_POST['data_ids']);
                //debug($ids);
                $data_type = $_POST['data_type'];
                $tran_currency = $_POST['pe_currency'];
                $params = array(
                    'customer_id' =>$ids,
                    'tran_currency' =>$tran_currency
                );
            }

            //debug($params);
        }

        $data = $this->get_customers_report($params);
        //debug($data);

        $total_records = count($data['cutomer_sales']);

        $return_arr = array();
        foreach ($data['cutomer_sales'] as $key => $obj) {

            $return_arr[] = array(
                'id' => $key, // Customer id
                'cell' => array(
                    $key, // Customer id
                    $obj['info']['name'],
                    $this->formatNumber($obj['sales_totals']['tl_opening_bal'], 'money'),
                    $this->formatNumber($obj['sales_totals']['tl_undue'], 'money'),
                    $this->formatNumber($obj['sales_totals']['tl_pastdue'], 'money'),
                    $this->formatNumber($obj['sales_totals']['tl_repayment'], 'money'),
                    $this->formatNumber($obj['sales_totals']['tl_principal_bal'], 'money'),
                    $this->formatNumber($obj['sales_totals']['tl_interest'], 'money'),
                    $this->formatNumber($obj['sales_totals']['tl_total_bal'], 'money')
                )
            );
        }
        $return_arr[] = array(
            'id' => 'tl',
            'cell' => array(
                'tl',
                "<strong>TOTAL</strong>",
                "<strong>" . $this->formatNumber($data['overall_totals']['overall_opening_bal'], 'money') . "</strong>",
                "<strong>" . $this->formatNumber($data['overall_totals']['overall_undue'], 'money') . "</strong>",
                "<strong>" . $this->formatNumber($data['overall_totals']['overall_pastdue'], 'money') . "</strong>",
                "<strong>" . $this->formatNumber($data['overall_totals']['overall_repayment'], 'money') . "</strong>",
                "<strong>" . $this->formatNumber($data['overall_totals']['overall_principal_bal'], 'money') . "</strong>",
                "<strong>" . $this->formatNumber($data['overall_totals']['overall_interest'], 'money') . "</strong>",
                "<strong>" . $this->formatNumber($data['overall_totals']['overall_total_bal'], 'money') . "</strong>"
            )
        );

        if ($data_type == 'get') {
            # disable the rendering of the layout
            $this->autoRender = false;
            $this->autoLayout = false;

            if ($data) {
                return json_encode(array('success' => true, 'total' => $total_records, 'page' => $page, 'rows' => $return_arr));
            }
            else {
                return json_encode(array('success' => false, 'total' => $total_records, 'page' => $page, 'rows' => array()));
            }
        }
        elseif ($data_type == 'print') { //print
            $this->layout = 'print_report_layout';

            //debug($return_arr);
            $print_title = 'Client Payments Details';
            $this->set(compact('return_arr', 'data_type', 'print_title', 'tran_currency'));
        }
        elseif ($data_type == 'export') { //export
            $this->layout = 'export_report_layout';

            //debug($return_arr);

            $print_title = 'Client Payments Details';
            $file_name = "client_payments_$tran_currency";
            $this->set(compact('return_arr', 'data_type', 'print_title', 'tran_currency', 'file_name'));
        }

    }


    /**
     *
     * @param void
     * @return html
     * @access public
     */
    function load($id=null,$currency=null, $data_type=null)
    {
        $data_type = isset($data_type) && $data_type !=null ? $data_type : 'load';
        if ($this->request->is('ajax')) {
            $customer_id = $_POST['data-id'];
            $currency = $_POST['currency'];
        }
        else{
            $customer_id = $id;
            $currency = $currency;
        }

        $params = array(
            'tran_currency' =>$currency,
            'customer_id' =>$customer_id,
        );
        $return_arr = $this->get_customers_report($params);

        if($data_type == 'load'){
            $this->autoRender = false;
            $this->autoLayout = false;

            if ($return_arr) {
                return json_encode(array('code' => 0, 'data' => $return_arr, 'mesg' => __('Data Found')));
            }
            else {
                return json_encode(array('code' => 1, 'data' => array(), 'mesg' => __('No Record Found')));
            }
        }
        else{
            $this->layout = 'print_report_layout';

            $name = $return_arr['cutomer_sales'][$customer_id]['info']['name'];
            //debug($return_arr);

            $print_title = 'Client Payments Details';
            $file_name = "payment_".$currency."_".$name;
            $tran_currency = $currency;
            $this->set(compact('customer_id','return_arr', 'data_type', 'print_title', 'tran_currency', 'file_name'));
        }
    }


    function get_customers_report($params)
    {
        if(!isset($params['sales_ids'])){
            $params['sales_ids'] = null;
        }
        if(!isset($params['tran_currency'])){
            $params['tran_currency'] = null;
        }
        if(!isset($params['customer_id'])){
            $params['customer_id'] = null;
        }
        if(!isset($params['filter_debtors'])){
            $params['filter_debtors'] = null;
        }
        if(!isset($params['sortname'])){
            $params['sortname'] = null;
        }
        if(!isset($params['sortorder'])){
            $params['sortorder'] = null;
        }
        if(!isset($params['start'])){
            $params['start'] = -1;
        }
        if(!isset($params['limit'])){
            $params['limit'] = null;
        }

        //debug($params);

        $conditions = array('Sale.deleted' => 'n');
        if ($params['sales_ids'] != null) {
            $conditions['Sale.id'] = $params['sales_ids'];
        }
        if ($params['tran_currency'] != null) {
            $conditions['Sale.currency'] = $params['tran_currency'];
        }
        if ($params['customer_id'] != null) {
            $conditions['Sale.customer_id'] = $params['customer_id'];
        }
        if ($params['filter_debtors'] != null) {
            $conditions['Sale.on_credit'] = $params['filter_debtors'];
        }

        $sortname=  $params['sortname'];
        $sortorder= $params['sortorder'];
        $start= $params['start'];
        $limit= $params['limit'];

        $query = array(
            'conditions' => $conditions,
            'contain' => array(
                'Customer' => array(
                    'fields' => array('Customer.id', 'Customer.name', 'Customer.contact_person'),
                ),
                'SaleItem' => array(
                    'fields' => array('SaleItem.item_id', 'SaleItem.quantity'),
                    'Item' => array(
                        'fields' => array('Item.name')
                    )
                ),
                'SaleCreditPayment' => array(
                    'fields' => array('SaleCreditPayment.paid_amount'),
                )
            ),
            //'order' => "Sale.$sortname $sortorder",
            //'limit' => $start . ',' . $limit,
            'recursive' => 2
        );

        //debug($start);
        if($start >= 0){
            $query['order'] = "Sale.$sortname $sortorder";
            $query['limit'] = $start . ',' . $limit;
        }
        //debug($start);
        //debug($query);

        $sales_data = $this->Sale->find('all', $query);

        $dataCount = $this->Sale->find('count', array(
            'conditions' => $conditions,
            'recursive' => -1
        ));

        $total_records = $dataCount;

        $customers_sales = array();

        foreach ($sales_data as $obj) {

            $duration_in_month = $obj['Sale']['duration_in_month'];
            $currency = $obj['Sale']['currency'];

            //cache items and qty
            $ct = count($obj['SaleItem']);
            $separator = '';
            if ($ct > 1) {
                $separator = ',';
            }
            $cache_qty = '';
            $cache_items = '';
            foreach ($obj['SaleItem'] as $sit) {
                $cache_qty .= $sit['quantity'] . $separator;
                $cache_items .= $sit['Item']['name'] . $separator;
            }
            $cache_qty = trim($cache_qty, ",");
            $cache_items = trim($cache_items, ",");

            $p_opening_bal = $obj['Sale']['opening_bal'];
			$purchase_type = $obj['Sale']['purchase_type'];
            //IF the customer made outright payment
            if($p_opening_bal == $obj['Sale']['deposit']){
                $p_opening_bal = 0.00;
            }

            $mth_inst = $this->getMonthlyPayment($obj['Sale']['interest'], $duration_in_month, $p_opening_bal, $obj['Sale']['tenor'],$obj['Sale']['interest_rate_decimal_places'],$purchase_type);
            //$discount_amt =  (doubleval($obj['Sale']['discount']) / 100) * doubleval($obj['Sale']['principal']);
            $deposite = doubleval($obj['Sale']['deposit']);

            $amor = $this->get_amortization_data($obj['Sale']['id']);
            $re_pay = $this->get_repayment($obj['Sale']['id']);

            //Total Creidt Payment
            $totalpayment = 0.00;
            foreach ($obj['SaleCreditPayment'] as $data) {
                $totalpayment += doubleval($data['paid_amount']);
            }

            $opening_bal = doubleval($amor['opening_bal_raw']);
            $tl_inerest_raw = doubleval($amor['tl_inerest_raw']);

            // Outstanding balance
            $prin_bal = (doubleval($obj['Sale']['opening_bal']) - $totalpayment);
            $total_bal = (doubleval($obj['Sale']['opening_bal'] + $tl_inerest_raw) - $totalpayment);

            $customers_sales[$obj['Customer']['id']]['info'] = $obj['Customer'];
            $customers_sales[$obj['Customer']['id']]['sales'][$obj['Sale']['id']] = array(
                'invoice_number' => $obj['Sale']['invoice_number'],
                'currency' => $currency,
                'opening_bal' => $this->formatNumber($obj['Sale']['opening_bal']),
                'repayment' => $this->formatNumber($totalpayment),
                'principal_bal' => $this->formatNumber($prin_bal),
                'total_bal' => $this->formatNumber($total_bal),
                'purchased_date' => $obj['Sale']['purchased_date'],
                'vehicle_no' => $obj['Sale']['vehicle_no'],
                'deposit' => $this->formatNumber($deposite),
                'mth_inst' => $this->formatNumber($mth_inst),
                'quantity' => $cache_qty,
                'items' => $cache_items,
                'cost' => $obj['Sale']['cost'],
                'interest' => $amor['tl_inerest_raw'],
                'interest_plus_loan' => $amor['tl_inerest_raw'] + $obj['Sale']['opening_bal'],
                'undue' => $re_pay['undue_raw'],
                'pastdue' => $re_pay['pastdue_raw'],
                'comments' => $obj['Sale']['comments'],
            );
        }

        //Sales totals for each customer
        $overall_opening_bal = 0.00;
        $overall_repayment = 0.00;
        $overall_principal_bal = 0.00;
        $overall_total_bal = 0.00;
        $overall_deposit = 0.00;
        $overall_mth_inst = 0.00;
        $overall_cost = 0.00;
        $overall_interest = 0.00;
        $overall_interest_plus_loan = 0.00;
        $overall_undue = 0.00;
        $overall_pastdue = 0.00;
        foreach ($customers_sales as $key => $val) {
            //Cedi totals
            $tl_opening_bal = 0.00;
            $tl_repayment = 0.00;
            $tl_principal_bal = 0.00;
            $tl_total_bal = 0.00;
            $tl_deposit = 0.00;
            $tl_mth_inst = 0.00;
            $tl_cost = 0.00;
            $tl_interest = 0.00;
            $tl_interest_plus_loan = 0.00;
            $tl_undue = 0.00;
            $tl_pastdue = 0.00;

            foreach ($val['sales'] as $st) {
                $tl_opening_bal = $tl_opening_bal + doubleval($st['opening_bal']);
                $tl_repayment = $tl_repayment + doubleval($st['repayment']);
                $tl_principal_bal = $tl_principal_bal + doubleval($st['principal_bal']);
                $tl_total_bal = $tl_total_bal + doubleval($st['total_bal']);
                $tl_deposit = $tl_deposit + doubleval($st['deposit']);
                $tl_mth_inst = $tl_mth_inst + doubleval($st['mth_inst']);
                $tl_cost = $tl_cost + doubleval($st['cost']);
                $tl_interest = $tl_interest + doubleval($st['interest']);
                $tl_interest_plus_loan = $tl_interest_plus_loan + doubleval($st['interest_plus_loan']);
                $tl_undue = $tl_undue + doubleval($st['undue']);
                $tl_pastdue = $tl_pastdue + doubleval($st['pastdue']);
            }

            $customers_sales[$key]['sales_totals'] = array(
                'tl_opening_bal' => $tl_opening_bal,
                'tl_repayment' => $tl_repayment,
                'tl_principal_bal' => $tl_principal_bal,
                'tl_total_bal' => $tl_total_bal,
                'tl_deposit' => $tl_deposit,
                'tl_mth_inst' => $tl_mth_inst,
                'tl_cost' => $tl_cost,
                'tl_interest' => $tl_interest,
                'tl_interest_plus_loan' => $tl_interest_plus_loan,
                'tl_undue' => $tl_undue,
                'tl_pastdue' => $tl_pastdue
            );

            //add to the overall
            $overall_opening_bal = $overall_opening_bal + $tl_opening_bal;
            $overall_repayment = $overall_repayment + $tl_repayment;
            $overall_principal_bal = $overall_principal_bal + $tl_principal_bal;
            $overall_total_bal = $overall_total_bal + $tl_total_bal;
            $overall_deposit = $overall_deposit + $tl_deposit;
            $overall_mth_inst = $overall_mth_inst + $tl_mth_inst;
            $overall_cost = $overall_cost + $tl_cost;
            $overall_interest = $overall_interest + $tl_interest;
            $overall_interest_plus_loan = $overall_interest_plus_loan + $tl_interest_plus_loan;
            $overall_undue = $overall_undue + $tl_undue;
            $overall_pastdue = $overall_pastdue + $tl_pastdue;

        }

        $rt = array(
            'cutomer_sales' => $customers_sales,
            'overall_totals' => array(
                'overall_opening_bal' => $overall_opening_bal,
                'overall_repayment' => $overall_repayment,
                'overall_principal_bal' => $overall_principal_bal,
                'overall_total_bal' => $overall_total_bal,
                'overall_deposit' => $overall_deposit,
                'overall_mth_inst' => $overall_mth_inst,
                'overall_cost' => $overall_cost,
                'overall_interest' => $overall_interest,
                'overall_interest_plus_loan' => $overall_interest_plus_loan,
                'overall_undue' => $overall_undue,
                'overall_pastdue' => $overall_pastdue,
                'total_records' => $total_records
            )
        );

        return $rt;
    }


    /**
     * @name get
     * @param void
     * @return Json Data
     */
    function get_debtors(){

        if ($this->request->is('ajax')) {
            # disable the rendering of the layout
            $this->autoRender = false;
            $this->autoLayout = false;

            /**  Get posted data */
            $page = isset($_POST['page']) ? $_POST['page'] : 1;
            /** The current page */
            $sortname = isset($_POST['sortname']) ? $_POST['sortname'] : 'id';
            /** Sort column */
            $sortorder = isset($_POST['sortorder']) ? $_POST['sortorder'] : 'desc';
            /** Sort order */
            $qtype = isset($_POST['qtype']) ? $_POST['qtype'] : '';
            /** Search column */
            $search_query = isset($_POST['query']) ? $_POST['query'] : '';
            /** Search string */
            $rp = isset($_POST['rp']) ? $_POST['rp'] : 10;
            $limit = $rp;
            $start = ($page - 1) * $rp;
            $currency = isset($_POST['currency']) ? $_POST['currency'] : 'GHc';
            $customer = null;
            if(isset($_POST['customer_id']) && $_POST['customer_id'] != 0){
                $customer = $_POST['customer_id'];
            }

            $params = array(
                'tran_currency' =>$currency,
                'customer_id' => $customer,
                'filter_debtors' => 'y',
                'sortname' =>$sortname,
                'sortorder' =>$sortorder,
                'start' =>$start,
                'limit' =>$limit,
            );

            $cust_report_data = $this->get_customers_report($params);

            //debug($cust_report_data);

            $total_records = $cust_report_data['overall_totals']['total_records'];

            if ($cust_report_data) {
                $return_arr = array();
                foreach ($cust_report_data['cutomer_sales'] as $obj) {
                    $customer_id = $obj['info']['id'];
                    $customer_name = $obj['info']['name'];

                    foreach ($obj['sales'] as $key => $saleObj) {
                        $return_arr[] = array(
                            //'id' => $customer_id,
                            'id' => $key,
                            'cell' => array(
                                $customer_id,
                                $customer_name,
                                $saleObj['quantity'],
                                $saleObj['items'],
                                $this->formatNumber($saleObj['cost'], 'money'),
                                $this->formatNumber($saleObj['deposit'], 'money'),
                                $this->formatNumber($saleObj['opening_bal'], 'money'),
                                $this->formatNumber($saleObj['interest'], 'money'),
                                $this->formatNumber($saleObj['interest_plus_loan'], 'money'),
                                $this->formatNumber($saleObj['repayment'], 'money'),
                                $this->formatNumber($saleObj['undue'], 'money'),
                                $this->formatNumber($saleObj['pastdue'], 'money'),
                                $this->formatNumber($saleObj['total_bal'], 'money'),
                                $saleObj['comments']

                                // $currency.' '.$this->formatNumber($obj['Sale']['opening_bal'],'money'),
                            )
                        );
                    }
                }

                $return_arr[] = array(
                    'id' => 'tl',
                    'cell' => array(
                        'tl',
                        '',
                        '',
                        '<strong>Total</strong>',
                        "<strong>" . $this->formatNumber($cust_report_data['overall_totals']['overall_cost'], 'money') . "</strong>",
                        "<strong>" . $this->formatNumber($cust_report_data['overall_totals']['overall_deposit'], 'money') . "</strong>",
                        "<strong>" . $this->formatNumber($cust_report_data['overall_totals']['overall_opening_bal'], 'money') . "</strong>",
                        "<strong>" . $this->formatNumber($cust_report_data['overall_totals']['overall_interest'], 'money') . "</strong>",
                        "<strong>" . $this->formatNumber($cust_report_data['overall_totals']['overall_interest_plus_loan'], 'money') . "</strong>",
                        "<strong>" . $this->formatNumber($cust_report_data['overall_totals']['overall_repayment'], 'money') . "</strong>",
                        "<strong>" . $this->formatNumber($cust_report_data['overall_totals']['overall_undue'], 'money') . "</strong>",
                        "<strong>" . $this->formatNumber($cust_report_data['overall_totals']['overall_pastdue'], 'money') . "</strong>",
                        "<strong>" . $this->formatNumber($cust_report_data['overall_totals']['overall_total_bal'], 'money') . "</strong>",
                        ""
                    )
                );

                return json_encode(array('success' => true, 'total' => $total_records, 'page' => $page, 'rows' => $return_arr));
            }
            else {
                return json_encode(array('success' => false, 'total' => $total_records, 'page' => $page, 'rows' => array()));
            }
        }

        $customers_sales = $this->get_customers_select();

        $currency_types = $this->currency_types;

        $currency_opts = array();
        foreach ($currency_types as $key => $value) {
            $currency_opts[] = array('name' => $value, 'value' => $key);
        }

        $page_title = 'Debtors Report';

        $this->set(compact('page_title','customers_sales', 'currency_opts'));

    }


    /**
     * @name get
     * @param void
     * @return Json Data
     */
    function print_export_debtors(){
        $ids = explode(',', $_POST['data_ids']);
        //debug($ids);
        $data_type = $_POST['data_type'];
        $currency = $_POST['pe_currency'];
        $params = array(
            'sales_ids' =>$ids
        );
        $cust_report_data = $this->get_customers_report($params);

        //debug($cust_report_data);

        $return_arr = array();
        foreach ($cust_report_data['cutomer_sales'] as $obj) {
            $customer_id = $obj['info']['id'];
            $customer_name = $obj['info']['name'];

            foreach ($obj['sales'] as $key => $saleObj) {
                $return_arr[] = array(
                    'id' => $key,
                    'cell' => array(
                        $customer_id,
                        $customer_name,
                        $saleObj['quantity'],
                        $saleObj['items'],
                        $this->formatNumber($saleObj['cost'], 'money'),
                        $this->formatNumber($saleObj['deposit'], 'money'),
                        $this->formatNumber($saleObj['opening_bal'], 'money'),
                        $this->formatNumber($saleObj['interest'], 'money'),
                        $this->formatNumber($saleObj['interest_plus_loan'], 'money'),
                        $this->formatNumber($saleObj['repayment'], 'money'),
                        $this->formatNumber($saleObj['undue'], 'money'),
                        $this->formatNumber($saleObj['pastdue'], 'money'),
                        $this->formatNumber($saleObj['total_bal'], 'money'),
                        $saleObj['comments']

                        // $currency.' '.$this->formatNumber($obj['Sale']['opening_bal'],'money'),
                    )
                );
            }
        }

        $return_arr[] = array(
            'id' => 'tl',
            'cell' => array(
                'tl',
                '',
                '',
                '<strong>Total</strong>',
                "<strong>" . $this->formatNumber($cust_report_data['overall_totals']['overall_cost'], 'money') . "</strong>",
                "<strong>" . $this->formatNumber($cust_report_data['overall_totals']['overall_deposit'], 'money') . "</strong>",
                "<strong>" . $this->formatNumber($cust_report_data['overall_totals']['overall_opening_bal'], 'money') . "</strong>",
                "<strong>" . $this->formatNumber($cust_report_data['overall_totals']['overall_interest'], 'money') . "</strong>",
                "<strong>" . $this->formatNumber($cust_report_data['overall_totals']['overall_interest_plus_loan'], 'money') . "</strong>",
                "<strong>" . $this->formatNumber($cust_report_data['overall_totals']['overall_repayment'], 'money') . "</strong>",
                "<strong>" . $this->formatNumber($cust_report_data['overall_totals']['overall_undue'], 'money') . "</strong>",
                "<strong>" . $this->formatNumber($cust_report_data['overall_totals']['overall_pastdue'], 'money') . "</strong>",
                "<strong>" . $this->formatNumber($cust_report_data['overall_totals']['overall_total_bal'], 'money') . "</strong>",
                ""
            )
        );


        if ($data_type == 'print') { //print
            $this->layout = 'print_report_layout';

            //debug($return_arr);
            $print_title = 'Customers In Debt';
            $this->set(compact('return_arr', 'data_type', 'print_title', 'tran_currency'));
        }
        elseif ($data_type == 'export') { //export
            $this->layout = 'export_report_layout';

            //debug($return_arr);

            $print_title = 'Customers In Debt';
            $file_name = "customers_in_debt_$currency";

        }

        $tran_currency = $currency;

        $this->set(compact('return_arr', 'data_type', 'print_title', 'tran_currency', 'file_name'));

    }


    function aging_analysis()
    {

        if ($this->request->is('ajax')) {
            # disable the rendering of the layout
            $this->autoRender = false;
            $this->autoLayout = false;

            /**  Get posted data */
            $page = isset($_POST['page']) ? $_POST['page'] : 1;
            /** The current page */
            $sortname = isset($_POST['sortname']) ? $_POST['sortname'] : 'id';
            /** Sort column */
            $sortorder = isset($_POST['sortorder']) ? $_POST['sortorder'] : 'desc';
            /** Sort order */
            $qtype = isset($_POST['qtype']) ? $_POST['qtype'] : '';
            /** Search column */
            $search_query = isset($_POST['query']) ? $_POST['query'] : '';
            /** Search string */
            $rp = isset($_POST['rp']) ? $_POST['rp'] : 10;
            $limit = $rp;
            $start = ($page - 1) * $rp;
            $currency = isset($_POST['currency']) ? $_POST['currency'] : null;
            $customer_id = isset($_POST['customer_id']) && $_POST['customer_id'] != 0 ? $_POST['customer_id'] : null;

            $aging_data = $this->get_aging_analysis($currency, $customer_id, 'y');

            //debug($aging_data);

            $total_records = 1;

            if ($aging_data) {
                $return_arr = array();
                foreach ($aging_data['cutomer_sales'] as $obj) {
                    $customer_id = $obj['info']['id'];
                    $customer_name = $obj['info']['name'];

                    foreach ($obj['sales'] as $saleObj) {
                        $return_arr[] = array(
                            'id' => $customer_id,
                            'cell' => array(
                                $customer_id,
                                $customer_name,
                                $saleObj['purchase_dt'],
                                $saleObj['items'],
                                $this->formatNumber($saleObj['aging_30'], 'money'),
                                $this->formatNumber($saleObj['aging_60'], 'money'),
                                $this->formatNumber($saleObj['aging_90'], 'money')
                            )
                        );
                    }
                }

                $return_arr[] = array(
                    'id' => 'tl',
                    'cell' => array(
                        'tl',
                        '',
                        '',
                        '<strong>Total</strong>',
                        "<strong>" . $this->formatNumber($aging_data['overall_totals']['overall_aging_30'], 'money') . "</strong>",
                        "<strong>" . $this->formatNumber($aging_data['overall_totals']['overall_aging_60'], 'money') . "</strong>",
                        "<strong>" . $this->formatNumber($aging_data['overall_totals']['overall_aging_90'], 'money') . "</strong>",
                    )
                );

                return json_encode(array('success' => true, 'total' => $total_records, 'page' => $page, 'rows' => $return_arr));
            }
            else {
                return json_encode(array('success' => false, 'total' => $total_records, 'page' => $page, 'rows' => array()));
            }
        }

        $customers_sales = $this->get_customers_select();

        $currency_types = $this->currency_types;

        $currency_opts = array();
        foreach ($currency_types as $key => $value) {
            $currency_opts[] = array('name' => $value, 'value' => $key);
        }

        $page_title = 'Aging Analysis Report';

        $this->set(compact('page_title','customers_sales', 'currency_opts'));

    }


    function print_export_aging_analysis($currency=null ,$customer_id=null ,$data_type=null)
    {
        $customer_id = isset($customer_id) && $customer_id != 0 ? $customer_id : null;
        $aging_data = $this->get_aging_analysis($currency, $customer_id, 'y');

        $return_arr = array();
        foreach ($aging_data['cutomer_sales'] as $obj) {
            $customer_id = $obj['info']['id'];
            $customer_name = $obj['info']['name'];

            foreach ($obj['sales'] as $saleObj) {
                $return_arr[] = array(
                    'id' => $customer_id,
                    'cell' => array(
                        $customer_id,
                        $customer_name,
                        $saleObj['purchase_dt'],
                        $saleObj['items'],
                        $this->formatNumber($saleObj['aging_30'], 'money'),
                        $this->formatNumber($saleObj['aging_60'], 'money'),
                        $this->formatNumber($saleObj['aging_90'], 'money')
                    )
                );
            }
        }

        $return_arr[] = array(
            'id' => 'tl',
            'cell' => array(
                'tl',
                '',
                '',
                '<strong>Total</strong>',
                "<strong>" . $this->formatNumber($aging_data['overall_totals']['overall_aging_30'], 'money') . "</strong>",
                "<strong>" . $this->formatNumber($aging_data['overall_totals']['overall_aging_60'], 'money') . "</strong>",
                "<strong>" . $this->formatNumber($aging_data['overall_totals']['overall_aging_90'], 'money') . "</strong>",
            )
        );

        if ($data_type == 'print') { //print
            $this->layout = 'print_report_layout';

            //debug($return_arr);
            $print_title = 'Aging Analysis';
            $this->set(compact('return_arr', 'data_type', 'print_title', 'tran_currency'));
        }
        elseif ($data_type == 'export') { //export
            $this->layout = 'export_report_layout';

            //debug($return_arr);

            $print_title = 'Aging Analysis';
            $file_name = "customers_in_debt_$currency";

        }

        $tran_currency = $currency;

        $this->set(compact('return_arr', 'data_type', 'print_title', 'tran_currency', 'file_name'));
    }



    function get_aging_analysis($tran_currency = null, $customer_id = null, $filter_debtors = null)
    {

        $conditions = array('Sale.deleted' => 'n');
        if ($tran_currency != null) {
            $conditions['Sale.currency'] = $tran_currency;
        }
        if ($customer_id != null) {
            $conditions['Sale.customer_id'] = $customer_id;
        }
        if ($filter_debtors != null) {
            $conditions['Sale.on_credit'] = $filter_debtors;
        }

        $sales_data = $this->Sale->find('all', array(
            //'fields' => array('DISTINCT Sale.customer_id'),
            'conditions' => $conditions,
            'contain' => array(
                'Customer' => array(
                    'fields' => array('Customer.id', 'Customer.name', 'Customer.contact_person'),
                ),
                'SaleItem' => array(
                    'fields' => array('SaleItem.item_id', 'SaleItem.quantity'),
                    'Item' => array(
                        'fields' => array('Item.name')
                    )
                ),
                'SaleCreditPayment' => array(
                    'fields' => array('SaleCreditPayment.paid_amount'),
                )
            ),
            'recursive' => 2
        ));

        $customers_sales = array();

        foreach ($sales_data as $obj) {

            $duration_in_month = $obj['Sale']['duration_in_month'];
            $currency = $obj['Sale']['currency'];
            $sale_id = $obj['Sale']['id'];

            $items = '';
            foreach ($obj['SaleItem'] as $item) {
                $items .= $item['Item']['name'] . '(qty x' . $item['quantity'] . '),&nbsp;&nbsp;';
            }

            $aging = $this->get_aging_analysis_data($sale_id);

            $customers_sales[$obj['Customer']['id']]['info'] = $obj['Customer'];
            $customers_sales[$obj['Customer']['id']]['sales'][$obj['Sale']['id']] = array(
                'currency' => $currency,
                'items' => $items,
                'purchase_dt' => $obj['Sale']['purchased_date'],
                'aging_30' => $this->formatNumber($aging['aging_30']),
                'aging_60' => $this->formatNumber($aging['aging_60']),
                'aging_90' => $this->formatNumber($aging['aging_90'])
            );
        }


        //Sales totals for each customer
        $overall_aging_30 = 0.00;
        $overall_aging_60 = 0.00;
        $overall_aging_90 = 0.00;

        foreach ($customers_sales as $key => $val) {
            //Cedi totals
            $tl_aging_30 = 0.00;
            $tl_aging_60 = 0.00;
            $tl_aging_90 = 0.00;

            foreach ($val['sales'] as $st) {
                $tl_aging_30 = $tl_aging_30 + doubleval($st['aging_30']);
                $tl_aging_60 = $tl_aging_60 + doubleval($st['aging_60']);
                $tl_aging_90 = $tl_aging_90 + doubleval($st['aging_90']);
            }

            $customers_sales[$key]['sales_totals'] = array(
                'tl_aging_30' => $tl_aging_30,
                'tl_aging_60' => $tl_aging_60,
                'tl_aging_90' => $tl_aging_90
            );

            //add to the overall
            $overall_aging_30 = $overall_aging_30 + $tl_aging_30;
            $overall_aging_60 = $overall_aging_60 + $tl_aging_60;
            $overall_aging_90 = $overall_aging_90 + $tl_aging_90;
        }

        $rt = array(
            'cutomer_sales' => $customers_sales,
            'overall_totals' => array(
                'overall_aging_30' => $overall_aging_30,
                'overall_aging_60' => $overall_aging_60,
                'overall_aging_90' => $overall_aging_90
            )
        );

        return $rt;
    }


    function get_interest_analysis($params)
    {
        if(!isset($params['repayment_ids'])){
            $params['repayment_ids'] = null;
        }
        if(!isset($params['tran_currency'])){
            $params['tran_currency'] = null;
        }
        if(!isset($params['customer_id'])){
            $params['customer_id'] = null;
        }
        if(!isset($params['filter_debtors'])){
            $params['filter_debtors'] = null;
        }
        if(!isset($params['sortname'])){
            $params['sortname'] = null;
        }
        if(!isset($params['sortorder'])){
            $params['sortorder'] = null;
        }
        if(!isset($params['start'])){
            $params['start'] = -1;
        }
        if(!isset($params['limit'])){
            $params['limit'] = null;
        }

        $conditions = array(
            'SaleCreditPayment.deleted' => 'n',
            'SaleCreditPayment.pay_date >=' => $params['start_dt'],
            'SaleCreditPayment.pay_date <=' => $params['end_dt']
        );
        if ($params['repayment_ids'] != null) {
            $conditions['SaleCreditPayment.id'] = $params['repayment_ids'];
        }
        if ($params['tran_currency'] != null) {
            $conditions['Sale.currency'] = $params['tran_currency'];
        }
        if ($params['customer_id'] != null) {
            $conditions['Sale.customer_id'] = $params['customer_id'];
        }
        if ($params['filter_debtors'] != null) {
            $conditions['Sale.on_credit'] = $params['filter_debtors'];
        }

        $sortname=  $params['sortname'];
        $sortorder= $params['sortorder'];
        $start= $params['start'];
        $limit= $params['limit'];

        $query = array(
            'fields' => array('SaleCreditPayment.id','SaleCreditPayment.pay_date','SaleCreditPayment.paid_amount','SaleCreditPayment.sale_id'),
            'conditions' => $conditions,
            'contain' => array(
                'Sale'=>array(
                    'fields' => array('Sale.id','Sale.customer_id','Sale.purchase_type','Sale.opening_bal','Sale.interest','Sale.interest_rate_decimal_places','Sale.tenor','Sale.duration_in_month','Sale.invoice_number','Sale.vehicle_no','Sale.deposit','Sale.purchased_date','Sale.end_date'),
                    'Customer' => array(
                        'fields' => array('Customer.id', 'Customer.name', 'Customer.contact_person'),
                    ),
                ),
            ),
            'order' => array('SaleCreditPayment.id'=>'desc'),
            'recursive' => 2
        );

        //debug($start);
        if($start >= 0){
            $query['order'] = "SaleCreditPayment.$sortname $sortorder";
            $query['limit'] = $start . ',' . $limit;
        }

        $sales_data = $this->SaleCreditPayment->find('all', $query);
        $dataCount = $this->SaleCreditPayment->find('count', array(
            'conditions' => $conditions,
            'recursive' => 1
        ));
        $new_data = array();
        foreach($sales_data as $sd){
            $sale_id = $sd['Sale']['id'];
            $new_data[$sale_id]['Sale']=$sd['Sale'];
            $new_data[$sale_id]['Customer']=$sd['Sale']['Customer'];
            $new_data[$sale_id]['SaleCreditPayment'][] =$sd['SaleCreditPayment'];
        }

        $repayment = array();

        //Process the data
        foreach($new_data as $nsd){
            $sale_id = $nsd['Sale']['id'];
            $opening_bal = $nsd['Sale']['opening_bal'];
			$interest = $nsd['Sale']['interest'];
            $interest_rate_decimal_places = $nsd['Sale']['interest_rate_decimal_places'];
			$tenor = $nsd['Sale']['tenor'];
			$duration_in_month = $nsd['Sale']['duration_in_month'];
			$invoice_number = $nsd['Sale']['invoice_number'];
			$purchased_date = $nsd['Sale']['purchased_date'];
			$end_date = $nsd['Sale']['end_date'];
            $vehicle_no = $nsd['Sale']['vehicle_no'];
            $customer = $nsd['Customer']['name'];
			$purchase_type = $nsd['Sale']['purchase_type'];

            $is_outright = false;
            //IF the customer made outright payment
            if($opening_bal == $nsd['Sale']['deposit']){
                $opening_bal = 0.00;
                $is_outright = true;
            }
            $mth_inst = $this->getMonthlyPayment($interest, $duration_in_month, $opening_bal, $tenor,$interest_rate_decimal_places,$purchase_type);
            $interest_ratio = $this->getRate($interest, $tenor,$interest_rate_decimal_places);

            if($is_outright){//If it is outright payment
                $repay = $nsd['SaleCreditPayment'][0];
                $arr = array(
                    'sale_id'=> $sale_id,
                    'repayment_id'=> $repay['id'],
                    'vehicle_no'=>$vehicle_no,
                    'customer'=>$customer,
                    'date'=>$repay['pay_date'],
                    'pay_date'=>$repay['pay_date'],
                    'sale_no'=> $invoice_number,
                    'period'=>$this->__add_ordinal_suffix(1) . " month",
                    'mth_inst'=>0.00,
                    'actual_paid'=>$this->formatNumber($repay['paid_amount']),
                    'outstanding'=>0.00,
                    'expected_interest'=>0.00,//Expected Interest
                    'actual_interest'=>0.00,//Actual Interest == Expected interest if payment was made
                    'interest_balance'=>0.00,
                    'amor_principal'=>0.00,
                    'amor_balance'=>0.00

                );
                $repayment[] = $arr;
            }
            else{
                //Repayment formatting
                if ($duration_in_month > 0) { //Purchase date and end date are not the same
                    $new_dt = new DateTime($purchased_date);
                    $new_dt->modify('next month');
                    $start_date = $new_dt->format('Y-m-d'); //Smaller date
                    $end_date = $end_date; //Bigger date
                    $diff_in_month = $this->count_months_between_dates($start_date, $end_date);
                    $dates_arr = array($start_date);
                    if ($diff_in_month > 0) {
                        $date_increment = $start_date;
                        for ($x = 1; $x <= $diff_in_month; $x++) {
                            $dt = new DateTime($date_increment);
                            $dt->modify('next month');
                            $dates_arr[] = $date_increment = $dt->format('Y-m-d');
                        }
                    }
                    $count = 1;
                    $new_opbl = $opening_bal;//For amortization
                    foreach ($dates_arr as $date_str) {
                        $n_dt = new DateTime($date_str);
                        $rest = substr($date_str, 0, -3);
                        $amt_to_pay = 0.00;
                        $payment_made = false;
                        $pay_date = '';
                        $repayment_id = '';
                        foreach ($nsd['SaleCreditPayment'] as $payment) {
                            $y_m = substr($payment['pay_date'], 0, -3);
                            if ($rest == $y_m) {
                                $pay_date = $payment['pay_date'];
                                $repayment_id = $payment['id'];
                                $amt_to_pay = doubleval($payment['paid_amount']);
                                $payment_made = true;
                            }
                        }
                        /*************************************** Amortizing  */
                        $payment_amount = $mth_inst;
                        $inerest = $new_opbl * $interest_ratio;
                        $principal = $payment_amount - $inerest;
                        $balance = $bal1 = $new_opbl - $principal;
                        $bal1 = $this->formatNumber($bal1);
                        if ($bal1 == -0.0) {
                            $balance = 0.0;
                        }
                        //Reset the new opening bal
                        $new_opbl = $balance;

                        $expected_interest = $inerest;
                        $actual_interest = 0.00;
                        $interest_balance = 0.00;
                        if($payment_made){
                            if($amt_to_pay > $expected_interest){
                                $actual_interest = $inerest;
                            }
                            else{
                                $actual_interest = $amt_to_pay;
                            }
                        }
                        $interest_balance = $expected_interest - $actual_interest;
                        $amor_principal = $principal;
                        $amor_balance = $balance;

                        /**************************************** End of Amortizing */
                        $month_year = $n_dt->format('M-y');
                        $month_year2 = $n_dt->format('n-Y');
                        $month_year3 = $n_dt->format('Y-m-d');
                        $paid_amt = $this->formatNumber($amt_to_pay);
                        $expected = $this->formatNumber($mth_inst);
                        $outstanding = $this->formatNumber($mth_inst - $paid_amt);
                        if($payment_made){
                            $arr = array(
                                'sale_id'=> $sale_id,
                                'repayment_id'=> $repayment_id,
                                'vehicle_no'=>$vehicle_no,
                                'customer'=>$customer,
                                'date'=>$month_year3,
                                'pay_date'=>$pay_date,
                                'sale_no'=> $invoice_number,
                                'period'=>$this->__add_ordinal_suffix($count) . " month",
                                'mth_inst'=>$this->formatNumber($expected),
                                'actual_paid'=>$this->formatNumber($paid_amt),
                                'outstanding'=>$this->formatNumber($outstanding),
                                'expected_interest'=>$this->formatNumber($expected_interest),//Expected Interest
                                'actual_interest'=>$this->formatNumber($actual_interest),//Actual Interest == Expected interest if payment was made
                                'interest_balance'=>$interest_balance,
                                'amor_principal'=>$this->formatNumber($amor_principal),
                                'amor_balance'=>$this->formatNumber($amor_balance)

                            );
                            $repayment[] = $arr;
                        }

                        $count++;
                    }

                }
            }

        }

        $rt = array(
            'data'=>$repayment,
            'count'=>$dataCount
        );
        /** Loading */
        return $rt;
    }


    function interest_analysis(){
        if ($this->request->is('ajax')) {
            $this->autoRender = false;
            $this->autoLayout = false;

            /**  Get posted data */
            $page = isset($_POST['page']) ? $_POST['page'] : 1;
            /** The current page */
            $sortname = isset($_POST['sortname']) ? $_POST['sortname'] : 'id';
            /** Sort column */
            $sortorder = isset($_POST['sortorder']) ? $_POST['sortorder'] : 'desc';
            /** Sort order */
            $qtype = isset($_POST['qtype']) ? $_POST['qtype'] : '';
            /** Search column */
            $search_query = isset($_POST['query']) ? $_POST['query'] : '';
            /** Search string */
            $rp = isset($_POST['rp']) ? $_POST['rp'] : 10;
            $limit = $rp;
            $start = ($page - 1) * $rp;

            $currency = isset($_POST['currency']) ? $_POST['currency'] : 'GHc';
            $start_dt = isset($_POST['start_dt']) ? $this->covertDate($_POST['start_dt'],'mysql') : '';
            $end_dt = isset($_POST['end_dt']) ? $this->covertDate($_POST['end_dt'],'mysql') : '';

            $params = array(
                'tran_currency' =>$currency,
                'sortname' =>$sortname,
                'sortorder' =>$sortorder,
                'start' =>$start,
                'limit' =>$limit,
                'start_dt' => $start_dt,
                'end_dt' =>$end_dt
            );

            $sale_data = $this->get_interest_analysis($params);

            $tl_mth_inst = 0.00;
			$tl_actual_paid = 0.00;
			$tl_outstanding = 0.00;
			$tl_expected_interest = 0.00;
			$tl_actual_interest = 0.00;
			$tl_amor_principal = 0.00;
			$tl_amor_balance = 0.00;
            $tl_interest_balance = 0.00;

            $return_arr = array();
            foreach ($sale_data['data'] as $obj) {
                $tl_mth_inst = $tl_mth_inst + $obj['mth_inst'];
                $tl_actual_paid = $tl_actual_paid + $obj['actual_paid'];
                $tl_outstanding = $tl_outstanding + $obj['outstanding'];
                $tl_expected_interest = $tl_expected_interest + $obj['expected_interest'];
                $tl_actual_interest = $tl_actual_interest + $obj['actual_interest'];
                $tl_interest_balance = $tl_interest_balance + $obj['interest_balance'];
                $tl_amor_principal = $tl_amor_principal + $obj['amor_principal'];
                $tl_amor_balance = $tl_amor_balance + $obj['amor_balance'];


                $return_arr[] = array(
                    'id' => $obj['repayment_id'],
                    'cell' => array(
                        $obj['repayment_id'],
                        $obj['date'],
                        $obj['customer'],
                        $obj['vehicle_no'],
                        $obj['sale_no'],
                        $obj['period'],
                        $this->formatNumber($obj['mth_inst'], 'money'),
                        $this->formatNumber($obj['actual_paid'], 'money'),
                        //$this->formatNumber($obj['outstanding'], 'money'),
                        $this->formatNumber($obj['actual_interest'], 'money'),
                        $this->formatNumber($obj['interest_balance'], 'money'),
                        $this->formatNumber($obj['amor_principal'], 'money'),
                    )
                );
            }

            $return_arr[] = array(
                'id' => 'tl',
                'cell' => array(
                    'tl',
                    '',
                    '',
                    '',
                    '',
                    '<strong>Total</strong>',
                    "<strong>" . $this->formatNumber($tl_mth_inst, 'money') . "</strong>",
                    "<strong>" . $this->formatNumber($tl_actual_paid, 'money') . "</strong>",
                    "<strong>" . $this->formatNumber($tl_actual_interest, 'money') . "</strong>",
                    "<strong>" . $this->formatNumber($tl_interest_balance, 'money') . "</strong>",
                    "<strong>" . $this->formatNumber($tl_amor_principal, 'money') . "</strong>",
                )
            );

            $total_records = $sale_data['count'];

            if ($sale_data['data']) {
                return json_encode(array('success' => true, 'total' => $total_records, 'page' => $page, 'rows' => $return_arr));
            }
            else {
                return json_encode(array('success' => false, 'total' => $total_records, 'page' => $page, 'rows' => array()));
            }
        }

        $currency_types = $this->currency_types;

        $currency_opts = array();
        foreach ($currency_types as $key => $value) {
            $currency_opts[] = array('name' => $value, 'value' => $key);
        }

        $page_title = 'Financial Reporting';
        $this->set(compact('page_title','report_data','currency_opts'));
    }


    function print_export_interest_analysis(){
        # disable the rendering of the layout
        $ids = explode(',', $_POST['data_ids']);
        $data_type = $_POST['data_type'];
        $currency = $_POST['pe_currency'];
        $start_dt = $this->covertDate($_POST['pe_start_dt'],'mysql');
        $end_dt = $this->covertDate($_POST['pe_end_dt'],'mysql');

        $params = array(
            'repayment_ids' =>$ids,
            'start_dt' => $start_dt,
            'end_dt' =>$end_dt
        );
        $sale_data = $this->get_interest_analysis($params);

        $tl_mth_inst = 0.00;
        $tl_actual_paid = 0.00;
        $tl_outstanding = 0.00;
        $tl_expected_interest = 0.00;
        $tl_actual_interest = 0.00;
        $tl_amor_principal = 0.00;
        $tl_amor_balance = 0.00;
        $tl_interest_balance = 0.00;

        $return_arr = array();
        foreach ($sale_data['data'] as $obj) {
            $tl_mth_inst = $tl_mth_inst + $obj['mth_inst'];
            $tl_actual_paid = $tl_actual_paid + $obj['actual_paid'];
            $tl_outstanding = $tl_outstanding + $obj['outstanding'];
            $tl_expected_interest = $tl_expected_interest + $obj['expected_interest'];
            $tl_actual_interest = $tl_actual_interest + $obj['actual_interest'];
            $tl_interest_balance = $tl_interest_balance + $obj['interest_balance'];
            $tl_amor_principal = $tl_amor_principal + $obj['amor_principal'];
            $tl_amor_balance = $tl_amor_balance + $obj['amor_balance'];


            $return_arr[] = array(
                'id' => $obj['repayment_id'],
                'cell' => array(
                    $obj['repayment_id'],
                    $obj['date'],
                    $obj['customer'],
                    $obj['vehicle_no'],
                    $obj['sale_no'],
                    $obj['period'],
                    $this->formatNumber($obj['mth_inst'], 'money'),
                    $this->formatNumber($obj['actual_paid'], 'money'),
                    //$this->formatNumber($obj['outstanding'], 'money'),
                    $this->formatNumber($obj['actual_interest'], 'money'),
                    $this->formatNumber($obj['interest_balance'], 'money'),
                    $this->formatNumber($obj['amor_principal'], 'money'),

                )
            );
        }

        $return_arr[] = array(
            'id' => 'tl',
            'cell' => array(
                'tl',
                '',
                '',
                '',
                '',
                '<strong>Total</strong>',
                "<strong>" . $this->formatNumber($tl_mth_inst, 'money') . "</strong>",
                "<strong>" . $this->formatNumber($tl_actual_paid, 'money') . "</strong>",
                "<strong>" . $this->formatNumber($tl_actual_interest, 'money') . "</strong>",
                "<strong>" . $this->formatNumber($tl_interest_balance, 'money') . "</strong>",
                "<strong>" . $this->formatNumber($tl_amor_principal, 'money') . "</strong>",
            )
        );


        if ($data_type == 'print') { //print
            $this->layout = 'print_report_layout';

            //debug($return_arr);
            $print_title = 'Interest Analysis From '.$start_dt.' To '.$end_dt;
            $this->set(compact('return_arr', 'data_type', 'print_title', 'tran_currency'));
        }
        elseif ($data_type == 'export') { //export
            $this->layout = 'export_report_layout';

            //debug($return_arr);

            $print_title = 'Financial Reporting';
            $file_name = "financial_rep_".$start_dt."_to_".$end_dt."";

        }

        $tran_currency = $currency;

        $this->set(compact('return_arr', 'data_type', 'print_title', 'tran_currency', 'file_name'));
    }


    function sales_per_item(){
        # disable the rendering of the layout
        if ($this->request->is('ajax')) {
            $this->autoRender = false;
            $this->autoLayout = false;

            /**  Get posted data */
            $page = isset($_POST['page']) ? $_POST['page'] : 1;
            /** The current page */
            $sortname = isset($_POST['sortname']) ? $_POST['sortname'] : 'id';
            /** Sort column */
            $sortorder = isset($_POST['sortorder']) ? $_POST['sortorder'] : 'desc';
            /** Sort order */
            $qtype = isset($_POST['qtype']) ? $_POST['qtype'] : '';
            /** Search column */
            $search_query = isset($_POST['query']) ? $_POST['query'] : '';
            /** Search string */
            $rp = isset($_POST['rp']) ? $_POST['rp'] : 10;
            $limit = $rp;
            $start = ($page - 1) * $rp;

            $currency = isset($_POST['currency']) ? $_POST['currency'] : 'GHc';
            $start_dt = isset($_POST['start_dt']) ? $this->covertDate($_POST['start_dt'],'mysql') : '';
            $end_dt = isset($_POST['end_dt']) ? $this->covertDate($_POST['end_dt'],'mysql') : '';

            $data = $this->get_sales_per_item($currency,$start_dt,$end_dt);

            $return_arr = array();
            foreach ($data['items'] as $key => $obj) {
                $return_arr[] = array(
                    'id' => $key,
                    'cell' => array(
                        $key,
                        $obj['name'],
                        $this->formatNumber($obj['tl_unit_price'], 'money'),
                        $this->formatNumber($obj['tl_discount_amount'], 'money'),
                        $this->formatNumber($obj['tl_discounted_unit_price'], 'money'),
                        $obj['tl_qty'],
                        $this->formatNumber($obj['tl_cost_price'], 'money'),
                       // $this->formatNumber($obj['tl_payment'], 'money'),
                        //$this->formatNumber($obj['tl_outstanding'], 'money')
                    )
                );
            }

            $return_arr[] = array(
                'id' => 'tl',
                'cell' => array(
                    'tl',
                    '<strong>Total</strong>',
                    "<strong>" . $this->formatNumber($data['totals']['tl_unit_price'], 'money') . "</strong>",
                    "<strong>" . $this->formatNumber($data['totals']['tl_discount_amount'], 'money') . "</strong>",
                    "<strong>" . $this->formatNumber($data['totals']['tl_discounted_unit_price'], 'money') . "</strong>",
                    "<strong>" .$data['totals']['tl_qty'] . "</strong>",
                    "<strong>" . $this->formatNumber($data['totals']['tl_cost_price'], 'money') . "</strong>",
                )
            );

            $total_records = count($data['items']);

            if ($data) {
                return json_encode(array('success' => true, 'total' => $total_records, 'page' => $page, 'rows' => $return_arr));
            }
            else {
                return json_encode(array('success' => false, 'total' => $total_records, 'page' => $page, 'rows' => array()));
            }
        }

        $currency_types = $this->currency_types;

        $currency_opts = array();
        foreach ($currency_types as $key => $value) {
            $currency_opts[] = array('name' => $value, 'value' => $key);
        }

        $page_title = 'Sales Items Report';

        $this->set(compact('page_title','customers_sales', 'currency_opts'));

    }


    function print_export_sales_per_item($pcurrency = null, $pdata_type = null, $pstart_dt=null, $pend_dt=null){
        # disable the rendering of the layout

        $currency = $pcurrency;
        $start_dt = $this->covertDate($pstart_dt,'mysql');
        $end_dt = $this->covertDate($pend_dt,'mysql');
        $data_type = $pdata_type;

        $data = $this->get_sales_per_item($currency,$start_dt,$end_dt);

        $return_arr = array();
        foreach ($data['items'] as $key => $obj) {
            $return_arr[] = array(
                'id' => $key,
                'cell' => array(
                    $key,
                    $obj['name'],
                    $this->formatNumber($obj['tl_unit_price'], 'money'),
                    $this->formatNumber($obj['tl_discount_amount'], 'money'),
                    $this->formatNumber($obj['tl_discounted_unit_price'], 'money'),
                    $obj['tl_qty'],
                    $this->formatNumber($obj['tl_cost_price'], 'money'),
                    // $this->formatNumber($obj['tl_payment'], 'money'),
                    //$this->formatNumber($obj['tl_outstanding'], 'money')
                )
            );
        }

        $return_arr[] = array(
            'id' => 'tl',
            'cell' => array(
                'tl',
                '<strong>Total</strong>',
                "<strong>" . $this->formatNumber($data['totals']['tl_unit_price'], 'money') . "</strong>",
                "<strong>" . $this->formatNumber($data['totals']['tl_discount_amount'], 'money') . "</strong>",
                "<strong>" . $this->formatNumber($data['totals']['tl_discounted_unit_price'], 'money') . "</strong>",
                "<strong>" .$data['totals']['tl_qty'] . "</strong>",
                "<strong>" . $this->formatNumber($data['totals']['tl_cost_price'], 'money') . "</strong>",
            )
        );


        $this->layout = 'print_report_layout';

        $print_title = 'Sales Per Item From '.$start_dt.' To '.$end_dt;
        $file_name = "sales_per_item_$currency From ".$start_dt.' To '.$end_dt;
        $this->set(compact('return_arr', 'data_type', 'print_title', 'currency', 'file_name'));
    }
}